"""2024.3.0 business colonne visible

Revision ID: 1b5ae0fd7e16
Revises: fad28b6bd362
Create Date: 2024-03-15 14:33:58.394004

"""

# revision identifiers, used by Alembic.
revision = "1b5ae0fd7e16"
down_revision = "bc80db375bde"

from alembic import op
import sqlalchemy as sa
from caerp.alembic.utils import (
    drop_index_if_exists,
    foreign_key_exists,
    table_exists,
    foreign_key_exists,
)
from caerp.models.project.business import Business


def update_database_structure():
    # ### commands auto generated by Alembic - please adjust! ###
    drop_index_if_exists(
        index_name="uq_oidc_redirect_uri_uri", table="oidc_redirect_uri"
    )
    if table_exists("oidc_redirect_uri"):
        op.drop_table("oidc_redirect_uri")
    drop_index_if_exists(
        index_name="uq_oidc_client__client_secret", table="oidc_client"
    )
    drop_index_if_exists(index_name="uq_oidc_client_client_id", table="oidc_client")
    drop_index_if_exists(index_name="uq_oidc_client_name", table="oidc_client")
    if table_exists("oidc_id_token"):
        op.drop_table("oidc_id_token")
    drop_index_if_exists(index_name="uq_oidc_code_authcode", table="oidc_code")
    if table_exists("oidc_code"):
        op.drop_table("oidc_code")
    drop_index_if_exists(index_name="uq_oidc_token_access_token", table="oidc_token")
    drop_index_if_exists(index_name="uq_oidc_token_refresh_token", table="oidc_token")
    if table_exists("oidc_token"):
        op.drop_table("oidc_token")
    if table_exists("oidc_client"):
        op.drop_table("oidc_client")
    op.add_column("business", sa.Column("visible", sa.Boolean(), nullable=True))
    if not foreign_key_exists("cancelinvoice", "fk_cancelinvoice_invoice_id"):
        op.create_foreign_key(
            op.f("fk_cancelinvoice_invoice_id"),
            "cancelinvoice",
            "invoice",
            ["invoice_id"],
            ["id"],
        )
    # ### end Alembic commands ###


def migrate_datas():
    from alembic.context import get_bind
    from zope.sqlalchemy import mark_changed
    from caerp_base.models.base import DBSESSION
    from caerp.models.task import Estimation, PaymentLine
    from caerp.models.project import Project, ProjectType

    session = DBSESSION()
    subq = (
        sa.select(sa.func.count(PaymentLine.id).label("count"), PaymentLine.task_id)
        .group_by(PaymentLine.task_id)
        .having(sa.func.count(PaymentLine.id) > 1)
        .subquery()
    )

    select_payment_lines = (
        sa.select(Estimation.business_id)
        .join_from(Estimation, subq)
        .where(Estimation.business_id != None)
    )
    business_ids = set(session.execute(select_payment_lines).scalars().all())

    select_deposit = (
        sa.select(Estimation.business_id)
        .where(Estimation.business_id != None)
        .where(Estimation.deposit > 0)
    )
    business_ids.union(set(session.execute(select_deposit).scalars().all()))

    op.execute(
        sa.update(Business)
        .where(Business.id.in_(business_ids))
        .values({"visible": True})
    )
    select_project_with_type = (
        sa.select(Project.id)
        .join(Project.project_type)
        .filter(ProjectType.with_business == True)
    )  # noqa

    op.execute(
        sa.update(Business)
        .where(Business.project_id.in_(select_project_with_type))
        .values({"visible": True})  # noqa
    )

    mark_changed(session)
    session.flush()


def upgrade():
    update_database_structure()
    migrate_datas()


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column("business", "visible")
    # ### end Alembic commands ###
